﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class BookInfoDao
    {
        public bool Delete(SqlConnection conn, int bookId)
        {
            string commandText = "DELETE FROM [dbo].[Book_Borrow] WHERE [BookId] = @BookId DELETE FROM [dbo].[Book_Info] WHERE [BookId] = @BookId";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BookId", SqlDbType.Int) };
            commandParameters[0].Value = bookId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }

        public int Insert(SqlConnection conn, BookInfoData data)
        {
            string commandText = "INSERT INTO [dbo].[Book_Info] ( [BookName], [BookNo], [DeptId], [TypeId], [Author], [ISBN], [PubHouse], [PubDate], [Area], [AMT], [Price], [Brief], [_Open], [Lend], [InputPerson], [Memo], [Photo], [AttachmentId] ) VALUES ( @BookName, @BookNo, @DeptId, @TypeId, @Author, @ISBN, @PubHouse, @PubDate, @Area, @AMT, @Price, @Brief, @_Open, @Lend, @InputPerson, @Memo, @Photo, @AttachmentId ) SELECT SCOPE_IDENTITY() AS RetValue";
            SqlParameter[] commandParameters = new SqlParameter[0x12];
            commandParameters[0] = new SqlParameter("@BookName", SqlDbType.NVarChar, 200);
            commandParameters[0].Value = data.BookName;
            commandParameters[1] = new SqlParameter("@BookNo", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.BookNo;
            commandParameters[2] = new SqlParameter("@DeptId", SqlDbType.Int);
            if (data.DeptIdIsNull)
            {
                commandParameters[2].Value = DBNull.Value;
            }
            else
            {
                commandParameters[2].Value = data.DeptId;
            }
            commandParameters[3] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[3].Value = data.TypeId;
            commandParameters[4] = new SqlParameter("@Author", SqlDbType.NVarChar, 200);
            if (data.AuthorIsNull)
            {
                commandParameters[4].Value = DBNull.Value;
            }
            else
            {
                commandParameters[4].Value = data.Author;
            }
            commandParameters[5] = new SqlParameter("@ISBN", SqlDbType.NVarChar, 200);
            if (data.ISBNIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.ISBN;
            }
            commandParameters[6] = new SqlParameter("@PubHouse", SqlDbType.NVarChar, 200);
            if (data.PubHouseIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.PubHouse;
            }
            commandParameters[7] = new SqlParameter("@PubDate", SqlDbType.DateTime);
            if (data.PubDateIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.PubDate;
            }
            commandParameters[8] = new SqlParameter("@Area", SqlDbType.NVarChar, 200);
            if (data.AreaIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.Area;
            }
            commandParameters[9] = new SqlParameter("@AMT", SqlDbType.Int);
            if (data.AMTIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.AMT;
            }
            commandParameters[10] = new SqlParameter("@Price", SqlDbType.NVarChar, 20);
            if (data.PriceIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.Price;
            }
            commandParameters[11] = new SqlParameter("@Brief", SqlDbType.NText);
            if (data.BriefIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Brief;
            }
            commandParameters[12] = new SqlParameter("@_Open", SqlDbType.NText);
            if (data._OpenIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data._Open;
            }
            commandParameters[13] = new SqlParameter("@Lend", SqlDbType.Int);
            commandParameters[13].Value = data.Lend;
            commandParameters[14] = new SqlParameter("@InputPerson", SqlDbType.NVarChar, 20);
            if (data.InputPersonIsNull)
            {
                commandParameters[14].Value = DBNull.Value;
            }
            else
            {
                commandParameters[14].Value = data.InputPerson;
            }
            commandParameters[15] = new SqlParameter("@Memo", SqlDbType.NText);
            if (data.MemoIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.Memo;
            }
            commandParameters[0x10] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Photo;
            }
            commandParameters[0x11] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.AttachmentId;
            }
            object obj2 = SqlHelper.ExecuteScalar(conn, CommandType.Text, commandText, commandParameters);
            if (obj2 != DBNull.Value)
            {
                return Convert.ToInt32(obj2);
            }
            return -1;
        }

        public DataTable SelectByBookId(SqlConnection conn, int bookId)
        {
            string commandText = "SELECT [Book_Info].*, [Book_Type].[TypeName], [Department].[DeptName], dbo.GetDeptNames([_Open]) AS ToName, dbo.GetUserName([InputPerson]) AS InputPersonName FROM [dbo].[Book_Info], [dbo].[Book_Type], [dbo].[Department] WHERE [Book_Info].[BookId] = @BookId AND [Book_Info].[TypeId] = [Book_Type].[TypeId] AND [Book_Info].[DeptId] = [Department].[DeptId]";
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@BookId", SqlDbType.Int) };
            commandParameters[0].Value = bookId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Info";
            return set.Tables[0];
        }

        public DataTable SelectPaged(SqlConnection conn, int pagesize, int pageindex, bool docount)
        {
            string commandText = "IF(@docount=1) SELECT COUNT([BookId]) FROM [dbo].[Book_Info],[dbo].[Department] WHERE [Book_Info].[DeptId] = [Department].[DeptId] ELSE BEGIN DECLARE @IndexTable TABLE(ID int identity(1,1),PK int) DECLARE @PageLowerBound int DECLARE @PageUpperBound int SET @PageLowerBound=(@pageindex-1)*@pagesize SET @PageUpperBound=@PageLowerBound+@pagesize SET rowcount @PageUpperBound INSERT INTO @IndexTable(PK) SELECT [BookId] FROM [dbo].[Book_Info] SELECT [Book_Info].*, [Department].[DeptName], [Book_Type].[TypeName], @pageindex AS pageindex FROM [dbo].[Book_Info], [dbo].[Department], [dbo].[Book_Type], @IndexTable t WHERE [Book_Info].[DeptId] = [Department].[DeptId] AND [Book_Info].[TypeId] = [Book_Type].[TypeId] AND [Book_Info].[BookId] = t.PK AND t.ID>@PageLowerBound AND t.ID<=@PageUpperBound ORDER BY [Book_Info].[BookId] DESC END";
            SqlParameter[] commandParameters = new SqlParameter[3];
            commandParameters[0] = new SqlParameter("@pagesize", SqlDbType.Int);
            commandParameters[0].Value = pagesize;
            commandParameters[1] = new SqlParameter("@pageindex", SqlDbType.Int);
            commandParameters[1].Value = pageindex;
            commandParameters[2] = new SqlParameter("@docount", SqlDbType.Bit);
            commandParameters[2].Value = docount;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, commandText, commandParameters);
            set.Tables[0].TableName = "Book_Info";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, BookInfoData data)
        {
            string commandText = "UPDATE [dbo].[Book_Info] SET [BookName] = @BookName, [BookNo] = @BookNo, [DeptId] = @DeptId, [TypeId] = @TypeId, [Author] = @Author, [ISBN] = @ISBN, [PubHouse] = @PubHouse, [PubDate] = @PubDate, [Area] = @Area, [AMT] = @AMT, [Price] = @Price, [Brief] = @Brief, [_Open] = @_Open, [Lend] = @Lend, [Memo] = @Memo, [AttachmentId] = @AttachmentId WHERE [BookId] = @BookId IF @Photo IS NOT NULL UPDATE [dbo].[Book_Info] SET [Photo] = @Photo WHERE [BookId] = @BookId";
            SqlParameter[] commandParameters = new SqlParameter[0x13];
            commandParameters[0] = new SqlParameter("@BookId", SqlDbType.Int);
            commandParameters[0].Value = data.BookId;
            commandParameters[1] = new SqlParameter("@BookName", SqlDbType.NVarChar, 200);
            commandParameters[1].Value = data.BookName;
            commandParameters[2] = new SqlParameter("@BookNo", SqlDbType.NVarChar, 200);
            commandParameters[2].Value = data.BookNo;
            commandParameters[3] = new SqlParameter("@DeptId", SqlDbType.Int);
            if (data.DeptIdIsNull)
            {
                commandParameters[3].Value = DBNull.Value;
            }
            else
            {
                commandParameters[3].Value = data.DeptId;
            }
            commandParameters[4] = new SqlParameter("@TypeId", SqlDbType.Int);
            commandParameters[4].Value = data.TypeId;
            commandParameters[5] = new SqlParameter("@Author", SqlDbType.NVarChar, 200);
            if (data.AuthorIsNull)
            {
                commandParameters[5].Value = DBNull.Value;
            }
            else
            {
                commandParameters[5].Value = data.Author;
            }
            commandParameters[6] = new SqlParameter("@ISBN", SqlDbType.NVarChar, 200);
            if (data.ISBNIsNull)
            {
                commandParameters[6].Value = DBNull.Value;
            }
            else
            {
                commandParameters[6].Value = data.ISBN;
            }
            commandParameters[7] = new SqlParameter("@PubHouse", SqlDbType.NVarChar, 200);
            if (data.PubHouseIsNull)
            {
                commandParameters[7].Value = DBNull.Value;
            }
            else
            {
                commandParameters[7].Value = data.PubHouse;
            }
            commandParameters[8] = new SqlParameter("@PubDate", SqlDbType.DateTime);
            if (data.PubDateIsNull)
            {
                commandParameters[8].Value = DBNull.Value;
            }
            else
            {
                commandParameters[8].Value = data.PubDate;
            }
            commandParameters[9] = new SqlParameter("@Area", SqlDbType.NVarChar, 200);
            if (data.AreaIsNull)
            {
                commandParameters[9].Value = DBNull.Value;
            }
            else
            {
                commandParameters[9].Value = data.Area;
            }
            commandParameters[10] = new SqlParameter("@AMT", SqlDbType.Int);
            if (data.AMTIsNull)
            {
                commandParameters[10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[10].Value = data.AMT;
            }
            commandParameters[11] = new SqlParameter("@Price", SqlDbType.NVarChar, 20);
            if (data.PriceIsNull)
            {
                commandParameters[11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[11].Value = data.Price;
            }
            commandParameters[12] = new SqlParameter("@Brief", SqlDbType.NText);
            if (data.BriefIsNull)
            {
                commandParameters[12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[12].Value = data.Brief;
            }
            commandParameters[13] = new SqlParameter("@_Open", SqlDbType.NText);
            if (data._OpenIsNull)
            {
                commandParameters[13].Value = DBNull.Value;
            }
            else
            {
                commandParameters[13].Value = data._Open;
            }
            commandParameters[14] = new SqlParameter("@Lend", SqlDbType.Int);
            commandParameters[14].Value = data.Lend;
            commandParameters[15] = new SqlParameter("@InputPerson", SqlDbType.NVarChar, 20);
            if (data.InputPersonIsNull)
            {
                commandParameters[15].Value = DBNull.Value;
            }
            else
            {
                commandParameters[15].Value = data.InputPerson;
            }
            commandParameters[0x10] = new SqlParameter("@Memo", SqlDbType.NText);
            if (data.MemoIsNull)
            {
                commandParameters[0x10].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x10].Value = data.Memo;
            }
            commandParameters[0x11] = new SqlParameter("@Photo", SqlDbType.Image);
            if (data.PhotoIsNull)
            {
                commandParameters[0x11].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x11].Value = data.Photo;
            }
            commandParameters[0x12] = new SqlParameter("@AttachmentId", SqlDbType.NVarChar, 50);
            if (data.AttachmentIdIsNull)
            {
                commandParameters[0x12].Value = DBNull.Value;
            }
            else
            {
                commandParameters[0x12].Value = data.AttachmentId;
            }
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, commandText, commandParameters);
            return true;
        }
    }
}

